﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model;

namespace DB
{
    public class DbSeat : IDbSeat
    {
        private DbConnection con;
        private SqlCommand dbCmd;


        public DbSeat()
        {
            con = DbConnection.GetInstance();
            dbCmd = null;
        }

        //''''''''''''''''''' SLET IKKE '''''''''''''''''''''
        //Kan bruges senere evt, da metoden illusterer hvordan man bruger 2 SqlDataReaderObjekter på en connection.
        //'''''''''''''''''''''''''''''''''''''''''''''''''''

        // Association til = Seat kan se dens biograf.
        // Association fra = Seat kan ikke se dens biograf. men et tomt objekt af cinemaHall.
        //public List<Seat> FindSeats(CinemaHall cinemaHallObj, bool retriveAssociation)
        //{
        //    List<Seat> seats = new List<Seat>();
        //    Seat seatObj = new Seat();

        //    SqlDataReader cinemaReader = null;
        //    SqlDataReader seatsReader = null;

        //    try
        //    {
        //        string select = "SELECT * FROM Seat_xp WHERE cinemaHall = @cinemaHall";
        //        string selectCinema = "SELECT * FROM CinemaHall_xp WHERE cName = @cName";

        //        Console.WriteLine(select + selectCinema);

        //        SqlCommand seatsCmd = new SqlCommand(select, con.Dbconn);
        //        SqlCommand cinemaCmd = new SqlCommand(selectCinema, con.Dbconn);

        //        seatsCmd.Parameters.AddWithValue("@cinemaHall", cinemaHallObj.Name);

        //        con.OpenConnection();

        //        using (seatsReader = seatsCmd.ExecuteReader())
        //        {
        //            cinemaCmd.Parameters.AddWithValue("@cName", cinemaHallObj.Name);
        //            while (seatsReader.Read())
        //            {
        //                cinemaReader = cinemaCmd.ExecuteReader();

        //                using (cinemaReader)
        //                {
        //                    cinemaReader.Read();

        //                    seatObj = BuildSeat(seatsReader, cinemaReader, retriveAssociation);

        //                    seats.Add(seatObj);
        //                }
        //            }
        //        }
        //    }
        //    catch (SqlException e)
        //    {
        //        Console.WriteLine(e.Message);
        //    }
        //    catch (Exception e)
        //    {
        //        Console.WriteLine(e.Message);
        //    }

        //    con.CloseConnection();
        //    seatsReader.Close();
        //    cinemaReader.Close();

        //    return seats;            
        //}

        //public Seat BuildSeat(SqlDataReader seatsReader, SqlDataReader cinemaReader, bool retriveAssociations)
        //{
        //    Seat seatObj = new Seat();
            
        //    try
        //    {
        //        seatObj.No = (Int32)seatsReader["no"];
        //        seatObj.RowNo = (Int32)seatsReader["rowNo"];
        //        if (retriveAssociations)
        //        {
        //            seatObj.CinemaHall = dbCinemaHall.BuildCinemaHall(cinemaReader);
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        Console.WriteLine("Error in DbSeat");
        //        Console.WriteLine(e.Message);
        //    }

        //    return seatObj;
        //}

        public Seat FindSeat(string seatId, bool retriveAssociation)
        {
            SqlDataReader dbReader = null;
            Seat seatObj = null;

            string select = "SELECT * FROM Seat_xp WHERE seatId = @seatId";
            Console.WriteLine(select);

            try
            {
                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@seatId", seatId);
                dbReader = dbCmd.ExecuteReader();
                if (dbReader.Read())
                {
                    seatObj = BuildSeatNew(dbReader);
                }
                else
                {
                }

                dbReader.Close();

                if (retriveAssociation && seatObj != null)
                {
                    string cinemaHallName = seatObj.CinemaHall.Name;
                    IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                    CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, false); // Ingen associering fra cinemahall til dens seats
                    seatObj.CinemaHall = cinemaHallObj;
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbSeat - FindSeat");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbSeat - FindSeat");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return seatObj;
        }

        public List<Seat> FindSeatsNew(string cName, bool retriveAssociation) //Finder en List af Seats for et cinemaHallObjekt. "Multi result"
        {
            List<Seat> seats = new List<Seat>();
            SqlDataReader dbReader = null;
            Seat seatObj = null;

            string selectSeats = "SELECT * FROM Seat_xp WHERE cinemaHall = @cName";

            Console.WriteLine(selectSeats);

            try
            {
                dbCmd = con.GetDbCommand(selectSeats);
                dbCmd.Parameters.AddWithValue("@cName", cName);
                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                {
                    seatObj = BuildSeatNew(dbReader);
                    seats.Add(seatObj);
                }

                dbReader.Close(); // lukkes da der kun kan være en åben af gangen her. i if-sætningen bliver der brugt en SqlDataReader igen inde på dbCinemaHall.

                if (retriveAssociation)
                {
                    foreach (Seat seatObj2 in seats)
                    {
                        string cinemaHallName = seatObj2.CinemaHall.Name;
                        IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                        CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, false); // Ingen associering fra cinemahall til dens seats
                        seatObj2.CinemaHall = cinemaHallObj;
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbSeat - FindSeatsNew");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbSeat - FindSeatsNew");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();
            return seats;
        }


        //''''''''''''''''''''''''''''''''''''''''
        //Skulle bruges ved table seatsTaken, men er ikke nødvendig da tabellen ikke bruges mere
        //''''''''''''''''''''''''''''''''''''''''

        //public List<Seat> FindSeatsTaken(Show show, bool retriveAssociation)
        //{
        //    List<Seat> seatsTaken = new List<Seat>();
        //    SqlDataReader dbReader = null;
        //    Seat seatObj = null;

        //    string select = "SELECT Seat_xp.seatId, no, rowNo, cinemaHall "
        //                  + "FROM Seat_xp, SeatsTaken_xp, Show_xp "
        //                  + "WHERE date = @dateTimeStart AND SeatsTaken_xp.showId = Show_xp.showId AND Seat_xp.seatId = SeatsTaken_xp.seatId";

        //    Console.WriteLine(select);

        //    try
        //    {
        //        dbCmd = con.GetDbCommand(select);
        //        dbCmd.Parameters.AddWithValue("@dateTimeStart",show.DateTimeStart);
        //        dbReader = dbCmd.ExecuteReader();

        //        while(dbReader.Read())
        //        {
        //            seatObj = BuildSeatNew(dbReader);
        //            seatsTaken.Add(seatObj);
        //        }
        //        dbReader.Close();

        //        if (retriveAssociation)
        //        {
        //            foreach (Seat seatObj2 in seatsTaken)
        //            {
        //                string cinemaHallName = seatObj2.CinemaHall.Name;
        //                IDbCinemaHall dbCinemaHall = new DbCinemaHall();
        //                CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, false); // Ingen associering fra cinemahall til dens seats
        //                seatObj2.CinemaHall = cinemaHallObj;
        //            }
        //        }
        //    }
        //    catch (SqlException e)
        //    {
        //        Console.WriteLine("sql Error in DbSeat");
        //        Console.WriteLine(e.Message);
        //    }
        //    catch (Exception e)
        //    {
        //        Console.WriteLine("Error in DbSeat");
        //        Console.WriteLine(e.Message);
        //    }

        //    con.CloseConnection();
        //    return seatsTaken;
        //}

        public List<Seat> FindSeatsTaken(Show show, bool retriveAssociation)
        {
            List<Seat> seatsTaken = new List<Seat>();
            SqlDataReader dbReader = null;
            Seat seatObj = null;

            string select = "SELECT Seat_xp.seatId, no, rowNo, cinemaHall "
                          + "FROM Seat_xp, Ticket_xp, Show_xp "
                          + "WHERE Ticket_xp.showId = @showId AND Ticket_xp.showId = Show_xp.showId AND Seat_xp.seatId = Ticket_xp.seatId";

            Console.WriteLine(select);
         
            try
            {
                IDbShow dbShow = new DbShow();
                int showId = dbShow.GetShowId(show);

                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@showId", showId);
                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                {
                    seatObj = BuildSeatNew(dbReader);
                    seatsTaken.Add(seatObj);
                }
                dbReader.Close();

                if (retriveAssociation)
                {
                    foreach (Seat seatObj2 in seatsTaken)
                    {
                        string cinemaHallName = seatObj2.CinemaHall.Name;
                        IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                        CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, false); // Ingen associering fra cinemahall til dens seats
                        seatObj2.CinemaHall = cinemaHallObj;
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbSeat");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbSeat");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();
            return seatsTaken;
        }

        //''''''''''''''''''''''''''''''''''''''''
        //Skulle bruges ved table seatsTaken, men er ikke nødvendig da tabellen ikke bruges mere
        //''''''''''''''''''''''''''''''''''''''''

        //public int InsertSeatsTaken(Show showObj, Seat seatObj)
        //{
        //    int rc = -1;
        //    string insert = @"INSERT INTO SeatsTaken_xp VALUES(@seatId, @showId)";
            
        //    IDbShow dbShow = new DbShow();
        //    int showId = dbShow.GetShowId(showObj);

        //    try
        //    {
        //        dbCmd = con.GetDbCommand(insert);
        //        dbCmd.Parameters.AddWithValue("@seatId", seatObj.SeatId);
        //        dbCmd.Parameters.AddWithValue("@showId", showId);

        //        rc = dbCmd.ExecuteNonQuery();
        //    }
        //    catch (SqlException e)
        //    {
        //        Console.WriteLine("sql Error in DbSeat - InsertSeatsTaken");
        //        Console.WriteLine(e.Message);
        //    }
        //    catch (Exception e)
        //    {
        //        Console.WriteLine("Error in DbSeat - InsertSeatsTaken");
        //        Console.WriteLine(e.Message);
        //    }

        //    con.CloseConnection();
        //    return rc;
        //}

        //''''''''''''''''''''''''''''''''''''''''
        //Skulle bruges ved table seatsTaken, men er ikke nødvendig da tabellen ikke bruges mere
        //''''''''''''''''''''''''''''''''''''''''

        //public int DeleteSeatsTaken(String seatId)
        //{
        //    int rc = -1;
        //    string delete = "DELETE FROM SeatsTaken_xp WHERE seatID = @seatID";
        //    Console.WriteLine(delete);

        //    try
        //    {
        //        dbCmd = con.GetDbCommand(delete);
        //        dbCmd.Parameters.AddWithValue("@seatId", seatId);
        //        rc = dbCmd.ExecuteNonQuery();
        //    }
        //    catch (SqlException sqlE)
        //    {
        //        Console.WriteLine("sql Error in DbSeat - DeleteSeatsTaken");
        //        Console.WriteLine(sqlE.Message);
        //    }
        //    catch (Exception e)
        //    {
        //        Console.WriteLine("Error in DbSeat - DeleteSeatsTaken");
        //        Console.WriteLine(e.Message);
        //    }

        //    con.CloseConnection();
        //    return rc;
        //}


        private Seat BuildSeatNew(SqlDataReader dbReader)
        {
            Seat seatObj = new Seat();
            CinemaHall cinemaObj = new CinemaHall();

            try
            {
                seatObj.SeatId = dbReader["seatId"].ToString();
                seatObj.No = (Int32)dbReader["no"];
                seatObj.RowNo = (Int32)dbReader["rowNo"];
                cinemaObj.Name = dbReader["cinemaHall"].ToString(); // Henter navnet på cinemaHall da det skal bruges senere ved evt retriveAssociation samt man kan se navnet på cinemaHall uden assocering.
                seatObj.CinemaHall = cinemaObj;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in building seatObj");
                Console.WriteLine(e.Message);
            }

            return seatObj;
        }
    }
}
